﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DX.CRC.Common.Entities;
using WxStockWinSvc.Models.Entities;
using System.Data;
using WxStockWinSvc.Commons;
using WxStockWinSvc.Models.Enums;

namespace WxStockWinSvc.DALs.Dbs
{
    class DalWxStock
    {
        internal static List<StockInfo4Load> LoadStockBaseInfos()
        {
            QueryObject qo = CommonUtil.CreateTextQO();
            qo.CmdText = @"select a.ExCode as ExCode_Field
	            , a.StockCode 
	            , (select max(a_x.Date) from CommonStockInfo a_x 
                    where a_x.IsActive = 1
                        and a_x.ExchangeCode = a.ExCode
                        and a_x.StockCode = a.StockCode) as MaxDate
            from StockBaseInfo a
            where a.IsActive = 1
            order by a.ExCode
                , a.StockCode desc;";

            return qo.xExecuteList<StockInfo4Load>();
        }

        internal static void InsertWxStock(ChinaExchangeCategory exCategory, List<DataRow> dataRows)
        {
            QueryObjectCollection qoColl = new QueryObjectCollection();
            for (int i = 0; i < dataRows.Count; i++)
            {
                QueryObject qo = CommonUtil.CreateTextQO();
                qo.CmdText = string.Format(@"INSERT INTO [CommonStockInfo]
                           ([StockCode]
                           ,[StockName]
                           ,[Date]
                           ,[ClosePrice]
                           ,[CeilingPrice]
                           ,[FloorPrice]
                           ,[OpenPrice]
                           ,[LastClosePrice]
                           ,[FloatPrice]
                           ,[FloatPercent]
                           ,[TurnOverRate]
                           ,[VolumnCount]
                           ,[VolumnAmount]
                           ,[MarketCap]
                           ,[MarketCapCirculate]
                           ,[ExchangeCode]
                           ,[IsActive]
                           ,[CreateTime]
                           ,[Remark])
                     VALUES
                           (@StockCode{0}
                           ,@StockName{0}
                           ,@Date{0}
                           ,@ClosePrice{0}
                           ,@CeilingPrice{0}
                           ,@FloorPrice{0}
                           ,@OpenPrice{0}
                           ,@LastClosePrice{0}
                           ,@FloatPrice{0}
                           ,@FloatPercent{0}
                           ,@TurnOverRate{0}
                           ,@VolumnCount{0}
                           ,@VolumnAmount{0}
                           ,@MarketCap{0}
                           ,@MarketCapCirculate{0}
                           ,@ExchangeCode{0}
                           ,1
                           ,GETUTCDATE()
                           ,N'');", i);

                qo.AddParam(string.Format("@StockCode{0}", i), dataRows[i]["股票代码"].ToString().Replace("'", string.Empty));
                qo.AddParam(string.Format("@StockName{0}", i), dataRows[i]["名称"]);
                qo.AddParam(string.Format("@Date{0}", i), dataRows[i]["日期"]);
                qo.AddParam(string.Format("@ClosePrice{0}", i), dataRows[i]["收盘价"]);
                qo.AddParam(string.Format("@CeilingPrice{0}", i), dataRows[i]["最高价"]);
                qo.AddParam(string.Format("@FloorPrice{0}", i), dataRows[i]["最低价"]);
                qo.AddParam(string.Format("@OpenPrice{0}", i), dataRows[i]["开盘价"]);
                qo.AddParam(string.Format("@LastClosePrice{0}", i), dataRows[i]["前收盘"]);
                qo.AddParam(string.Format("@FloatPrice{0}", i), dataRows[i]["涨跌额"]);
                qo.AddParam(string.Format("@FloatPercent{0}", i), dataRows[i]["涨跌幅"]);
                qo.AddParam(string.Format("@TurnOverRate{0}", i), dataRows[i]["换手率"]);
                qo.AddParam(string.Format("@VolumnCount{0}", i), dataRows[i]["成交量"]);
                qo.AddParam(string.Format("@VolumnAmount{0}", i), dataRows[i]["成交金额"]);
                qo.AddParam(string.Format("@MarketCap{0}", i), dataRows[i]["总市值"]);
                qo.AddParam(string.Format("@MarketCapCirculate{0}", i), dataRows[i]["流通市值"]);
                qo.AddParam(string.Format("@ExchangeCode{0}", i), exCategory);

                qoColl.Add(qo);
            }

            qoColl.xExecuteNonQuery();
        }

        internal static List<ScheduleStockEntity> LoadScheduleEntities()
        {
            QueryObject qo = CommonUtil.CreateTextQO();
            qo.CmdText = @"select StockCode
	                        , Date as StartTime
                            , ExchangeCode
                        from
                        (
	                        select row_number() over (partition by StockCode order by Date desc) as RowNum
		                        , StockCode
		                        , Date 
	                        from CommonStockInfo
	                        where IsActive = 1
                        ) a_x
                        where a_x.RowNum = 1;";

            return qo.xExecuteList<ScheduleStockEntity>();
        }

        internal static DataTable LoadLatestRecord()
        {
            QueryObject qo = CommonUtil.CreateTextQO();
            qo.CmdText = @"select a.StockCode
                , Max(Date) as MaxDate
                from CommonStockInfo01 a
                group by a.StockCode
                order by a.StockCode";

            return qo.xExecuteDataTable();
        }
    }
}
